<?php
/**
 * Eka Framework 0.3.0
 *
 * @copyright Copyright (c) 2008 Alexey Yu. Chuprina <656b61@gmail.com> (http://php-eka.googlecode.com)
 * @license http://www.opensource.org/licenses/mit-license.php MIT License
 *
 * @version $Id$
 */


require_once 'Eka/Db/Adapter/Pdo/Abstract.php';

/**
 * Eka_Db_Adapter_Pdo_Mysql
 *
 * @package Db
 * @subpackage Adapter
 */
class Eka_Db_Adapter_Pdo_Mysql extends Eka_Db_Adapter_Pdo_Abstract
{
	/**
	 * PDO type
	 *
	 * @var string
	 */
	protected $pdoType = 'mysql';


	/**
	 * Symbol the adapter uses for delimited identifiers
	 *
	 * @var strign
	 */
	protected $quoteIdentifierSymbol = '`';

	/**
	 * Returns the column descriptions for a table
	 *
	 * columns: array of column names =>
	 *     name => string; column name
	 *     position => number; ordinal position of column in table
	 *     type => string; SQL datatype name of column
	 *     default => string; default expression of column, null if none
	 *     null => boolean; true if column can have nulls
	 *     length => number; length of CHAR/VARCHAR
	 *     scale => number; scale of NUMERIC/DECIMAL
	 *     precision => number; precision of NUMERIC/DECIMAL
	 *     unsigned => boolean; unsigned property of an integer type
	 *     primary => boolean; true if column is part of the primary key
	 *     primary_position => integer; position of column in primary key
	 *     identity => integer; true if column is auto-generated with unique values
	 * foreign_keys: array of key names:
	 *     table => string; reference table name
	 *     foreign => array; columns name
	 *     reference => array; reference columns name
	 *
	 * @param string $tableName
	 * @param string $tableSchema
	 * @return array
	 */
	public function describeTable($tableName, $tableSchema = null)
	{
		if (null === $tableSchema) {
			$tableSchema = $this->getCurrentSchema();
		}
		$table = $this->quoteIdentifier($tableSchema . '.' . $tableName);
		$matches = array();
		
		// columns
		$columns = array();
		$i = 0;
		$p = 0;

		$rows = $this->query('DESCRIBE ' . $table)->fetchAllNum();
		foreach ($rows as $row) {
			$length = null;
			$precision = null;
			$unsigned = null;
			$primary = false;
			$primaryPosition = null;
			$identity = false;
			$enum = null;
			$unique = false;

			if (strpos($row[1], 'unsigned')) {
				$unsigned = true;
			}
			
			switch (true) {
				case preg_match('`^((?:var)?char)\((\d+)\)`', $row[1], $matches):
					$type = $matches[1];
					$length = $matches[2];
					$rtype = 'string';
					break;
				case preg_match('`^((?:tiny|medium|long)?(?:text|blob))$`', $row[1], $matches):
					$type = $matches[1];
					$rtype = 'string';
					break;
				case preg_match('`^((?:big|medium|small|tiny)?int)\((\d+)\)`', $row[1], $matches):
					$type = $matches[1];
					$length = $matches[2];
					$rtype = 'int';
					break;
				case preg_match('`^(float|decimal|double)\((\d+),(\d+)\)`', $row[1], $matches):
					$type = $matches[1];
					$length = $matches[2];
					$precision = $matches[3];
					$rtype = 'float';
					break;
				case preg_match('`^(enum|set)\(\'(.+)\'\)$`', $row[1], $matches):
					$type = $matches[1];
					$enum = explode('\',\'', $matches[2]);
					$rtype = 'enum';
					break;
				case preg_match('`^(date|time|datetime|timestamp|year)$`', $row[1], $matches):
					$type = $matches[1];
					$rtype = 'date';
					break;
				default:
					$type = $row[1];
					break;
			}
			
			if ($row[3] == 'PRI') {
				$primary = true;
				$primaryPosition = $p;
				if ($row[5] == 'auto_increment') {
					$identity = true;
				}
				$p++;
			} elseif ($row[3] == 'UNI') {
				$unique = true;
			}
			
			
			$null = (bool) ($row[2] == 'YES');
			$default = $row[4];
			
			if (!$identity && $default === null && !$null) {
				switch ($rtype) {
					case 'string':
						$default = '';
						break;
					case 'int':
					case 'float':
						$default = 0;
						break;
					case 'enum':
						$default = $enum[0];
						break;
					case 'date':
						switch ($type) {
							case 'date':
								$default = '0000-00-00';
								break;
							case 'time':
								$default = '00:00:00';
								break;
							case 'datetime':
							case 'timestamp':
								$default = '0000-00-00 00:00:00';
								break;
						}
						break;
					default:
						break;
				}
			}
			
			$columns[$row[0]] = array(
				'name' => $row[0],
				'position' => $i,
				'type' => $type,
				'rtype' => $rtype,
				'default' => $default,
				'null' => $null,
				'length' => $length,
				'precision' => $precision,
				'enum' => $enum,
				'unsigned' => $unsigned,
				'primary' => $primary,
				'primary_position' => $primaryPosition,
				'unique' => $unique,
				'identity' => $identity
				);

			$i++;
		}
		
		// foreign_keys
		$foreignKeys = array();

		$data = $this->query('SHOW CREATE TABLE ' . $table)->fetchColumn(1);
		preg_match_all('`CONSTRAINT (.+?) FOREIGN KEY \((.+?)\) REFERENCES (.+?) \((.+?)\)`', $data, $matches, PREG_SET_ORDER);

		$callback = array($this, 'unQuoteIdentifier');
		foreach ($matches as $foreign) {
			$fTable = $this->unQuoteIdentifier($foreign[3]);
			$fTable = explode('.', $fTable);
			if (count($fTable) > 1) {
				$fSchema = $fTable[0];
				$fTable = $fTable[1];
			} else {
				$fSchema = $tableSchema;
				$fTable = $fTable[0];
			}
			$foreignKeys[$this->unQuoteIdentifier($foreign[1])] = array(
				'schema' => $fSchema,
				'table' => $fTable,
				'foreign' => array_map($callback, explode(',', $foreign[2])),
				'reference' => array_map($callback, explode(',', $foreign[4]))
				);
		}
		
		// indexes
		$indexes = array();
		$rows = $this->query('SHOW INDEXES FROM ' . $table)->fetchAllNum();
		
		foreach ($rows as $row) {
			if ($row[2] == 'PRIMARY') {
				continue;
			}
			if (!isset($indexes[$row[2]])) {
				$indexes[$row[2]] = array(
					'name' => $row[2],
					'unique' => !(bool)$row[1],
					'columns' => array()
					);
			}
			$indexes[$row[2]]['columns'][] = $row[4];
		}
		
		
		return array('columns' => $columns, 'foreign_keys' => $foreignKeys, 'indexes' => $indexes);
	}

	/**
	 * Adds an adapter-specific LIMIT clause to the SELECT statement
	 *
	 * @param mixed $sql
	 * @param integer $count
	 * @param integer $offset
	 * @return string
	 */
	public function limit($sql, $count = null, $offset = 0)
	{
		if (null !== $count) {
			$intCount = (int)$count;
			if ($intCount <= 0) {
				include_once 'Eka/Db/Adapter/Exception.php';
				throw new Eka_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
			}

			$intOffset = intval($offset);
			if ($intOffset < 0) {
				include_once 'Eka/Db/Adapter/Exception.php';
				throw new Eka_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
			}

			$sql .= ' LIMIT ' . $intCount;
			if ($intOffset > 0) {
				$sql .= ' OFFSET ' . $intOffset;
			}
		}
		return $sql;
	}
}